package database

import (
	"context"
	"database/sql"
	"strconv"
	"strings"
	"time"

	"github.com/pkg/errors"

	"changeme/internal/models"
)

// ExtendedBaseDriver 扩展的基础驱动，提供更多通用功能
type ExtendedBaseDriver struct {
	BaseDriver
	capabilities DatabaseCapabilities
}

// ExecuteQueryCommon 通用查询执行逻辑
func (bd *ExtendedBaseDriver) ExecuteQueryCommon(
	ctx context.Context,
	query string,
	args []interface{},
	db *sql.DB,
) (*models.QueryResult, error) {
	if db == nil {
		return nil, ErrNotConnected
	}

	start := time.Now()
	rows, err := db.QueryContext(ctx, query, args...)
	if err != nil {
		return &models.QueryResult{
			Error:       err.Error(),
			ExecuteTime: time.Since(start),
		}, nil
	}
	defer rows.Close()

	// 获取列名
	columns, err := rows.Columns()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get columns")
	}

	// 获取列类型
	colTypes, err := rows.ColumnTypes()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get column types")
	}

	// 读取数据
	var result []map[string]interface{}
	for rows.Next() {
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))
		for i := range values {
			valuePtrs[i] = &values[i]
		}

		if err := rows.Scan(valuePtrs...); err != nil {
			return nil, errors.Wrap(err, "failed to scan row")
		}

		row := make(map[string]interface{})
		for i, col := range columns {
			val := values[i]
			row[col] = bd.processValue(val, colTypes[i])
		}
		result = append(result, row)
	}

	return &models.QueryResult{
		Columns:     columns,
		Rows:        result,
		RowCount:    len(result),
		ExecuteTime: time.Since(start),
	}, nil
}

// processValue 处理特殊类型值，统一处理JavaScript安全整数等问题
func (bd *ExtendedBaseDriver) processValue(val interface{}, colType *sql.ColumnType) interface{} {
	if val == nil {
		return nil
	}

	// 处理byte数组
	if b, ok := val.([]byte); ok {
		dbType := strings.ToLower(colType.DatabaseTypeName())

		// 对于大整数类型，转换为字符串以避免JS整数溢出
		if strings.Contains(dbType, "bigint") || strings.Contains(dbType, "int8") {
			return string(b)
		}

		// 对于DECIMAL/NUMERIC类型，转换为字符串保留精度
		if strings.Contains(dbType, "decimal") || strings.Contains(dbType, "numeric") || strings.Contains(dbType, "number") {
			return string(b)
		}

		// 尝试转换为适当的类型
		if strings.Contains(dbType, "int") {
			if intVal, err := strconv.ParseInt(string(b), 10, 64); err == nil {
				if intVal > 9007199254740991 || intVal < -9007199254740991 {
					return string(b) // 超出JS安全范围
				}
				return intVal
			}
		}

		if strings.Contains(dbType, "float") || strings.Contains(dbType, "double") || strings.Contains(dbType, "real") {
			if floatVal, err := strconv.ParseFloat(string(b), 64); err == nil {
				return floatVal
			}
		}

		// 默认转换为字符串
		return string(b)
	}

	// 处理int64类型
	if i64, ok := val.(int64); ok && (i64 > 9007199254740991 || i64 < -9007199254740991) {
		return strconv.FormatInt(i64, 10)
	}

	return val
}

// ExecuteNonQueryCommon 通用非查询执行逻辑
func (bd *ExtendedBaseDriver) ExecuteNonQueryCommon(ctx context.Context, query string, args []interface{}, db *sql.DB) (int64, error) {
	if db == nil {
		return 0, ErrNotConnected
	}

	result, err := db.ExecContext(ctx, query, args...)
	if err != nil {
		return 0, errors.Wrap(err, "failed to execute non-query")
	}

	affected, err := result.RowsAffected()
	if err != nil {
		return 0, errors.Wrap(err, "failed to get affected rows")
	}

	return affected, nil
}

// BeginTransactionCommon 通用事务开始逻辑
func (bd *ExtendedBaseDriver) BeginTransactionCommon(ctx context.Context, db *sql.DB) (*sql.Tx, error) {
	if db == nil {
		return nil, ErrNotConnected
	}

	tx, err := db.BeginTx(ctx, nil)
	if err != nil {
		return nil, errors.Wrap(err, "failed to begin transaction")
	}

	return tx, nil
}

// GetCapabilities 获取数据库能力
func (bd *ExtendedBaseDriver) GetCapabilities() DatabaseCapabilities {
	if bd.capabilities == nil {
		// 根据配置的数据库类型返回相应的能力
		if bd.Config != nil {
			bd.capabilities = GetCapabilities(bd.Config.Type)
		} else {
			bd.capabilities = &BaseCapabilities{}
		}
	}
	return bd.capabilities
}

// CommonTransaction 通用事务实现
type CommonTransaction struct {
	tx     *sql.Tx
	driver *ExtendedBaseDriver
}

func (ct *CommonTransaction) Commit() error {
	return ct.tx.Commit()
}

func (ct *CommonTransaction) Rollback() error {
	return ct.tx.Rollback()
}

func (ct *CommonTransaction) ExecuteQuery(ctx context.Context, query string, args ...interface{}) (*models.QueryResult, error) {
	start := time.Now()
	rows, err := ct.tx.QueryContext(ctx, query, args...)
	if err != nil {
		return &models.QueryResult{
			Error:       err.Error(),
			ExecuteTime: time.Since(start),
		}, nil
	}
	defer rows.Close()

	columns, err := rows.Columns()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get columns")
	}

	colTypes, err := rows.ColumnTypes()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get column types")
	}

	var result []map[string]interface{}
	for rows.Next() {
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))
		for i := range values {
			valuePtrs[i] = &values[i]
		}

		if err := rows.Scan(valuePtrs...); err != nil {
			return nil, errors.Wrap(err, "failed to scan row")
		}

		row := make(map[string]interface{})
		for i, col := range columns {
			val := values[i]
			row[col] = ct.driver.processValue(val, colTypes[i])
		}
		result = append(result, row)
	}

	return &models.QueryResult{
		Columns:     columns,
		Rows:        result,
		RowCount:    len(result),
		ExecuteTime: time.Since(start),
	}, nil
}

func (ct *CommonTransaction) ExecuteNonQuery(ctx context.Context, query string, args ...interface{}) (int64, error) {
	result, err := ct.tx.ExecContext(ctx, query, args...)
	if err != nil {
		return 0, errors.Wrap(err, "failed to execute non-query in transaction")
	}

	affected, err := result.RowsAffected()
	if err != nil {
		return 0, errors.Wrap(err, "failed to get affected rows in transaction")
	}

	return affected, nil
}

// 表设计相关的基础实现

// GenerateCreateTableSQL 生成创建表SQL（应由具体驱动覆盖）
func (bd *BaseDriver) GenerateCreateTableSQL(database string, tableSchema *models.TableSchema) (string, error) {
	return "", errors.New("GenerateCreateTableSQL not implemented for this database type")
}

// GenerateAlterTableSQL 生成修改表SQL（应由具体驱动覆盖）
func (bd *BaseDriver) GenerateAlterTableSQL(database, table string, alterSchema *models.TableAlterSchema) ([]string, error) {
	return nil, errors.New("GenerateAlterTableSQL not implemented for this database type")
}

// GenerateDropTableSQL 生成删除表SQL
func (bd *BaseDriver) GenerateDropTableSQL(database, table string, cascade bool) string {
	identifier := bd.formatTableIdentifier(database, table)
	sql := "DROP TABLE " + identifier
	if cascade {
		sql += " CASCADE"
	}
	return sql
}

// GenerateRenameTableSQL 生成重命名表SQL（应由具体驱动覆盖）
func (bd *BaseDriver) GenerateRenameTableSQL(database, oldTable, newTable string) string {
	return ""
}

// GenerateTruncateTableSQL 生成清空表SQL
func (bd *BaseDriver) GenerateTruncateTableSQL(database, table string) string {
	identifier := bd.formatTableIdentifier(database, table)
	return "TRUNCATE TABLE " + identifier
}

// GetTableDDL 获取表的DDL语句（应由具体驱动覆盖）
func (bd *BaseDriver) GetTableDDL(database, table string) (string, error) {
	return "", errors.New("GetTableDDL not implemented for this database type")
}

// formatTableIdentifier 格式化表标识符
func (bd *BaseDriver) formatTableIdentifier(database, table string) string {
	if database != "" {
		return bd.Dialect.QuoteIdentifier(database) + "." + bd.Dialect.QuoteIdentifier(table)
	}
	return bd.Dialect.QuoteIdentifier(table)
}
